<?php
/**
 * Description of class
 *
 * @author Márlon
 */
class Relatorios 
{
    function __construct()
    {
        require_once '../classes/class.ConexaoBD.php';
        require_once '../classes/class.alunosmatriculados.php';
    }

    function ReportTotalAlunos($pAno)
    {
        $ClsBD = new ConexaoBD();
        $strSQL = "SELECT C.Turma, C.Curso, MONTH(C.DtEfetivaMatricula) AS 'MES', Count(C.CPF) AS 'Alunos' FROM cadastro C ";
        $strSQL .= "WHERE YEAR(C.DtEfetivaMatricula) = '$pAno' ";
        $strSQL .= "GROUP BY C.Turma, C.Curso , MONTH(C.DtEfetivaMatricula) ";
        
        $Retorno = $ClsBD->ExecutarQry($strSQL, "S");
        return $Retorno;
    }
    
    function ReportHistorico($cCurso, $cTurma, $pNome, $pCpf)
    {
        $ClsBD = new ConexaoBD();
        
        $strSQL = "SELECT C.Nome, C.EMail, (DateDiff(C.DtFimCurso, C.DtIniCurso)+1)*2 AS 'Total Aulas', ";
        $strSQL .= "Count((case when P.Manha = 'P' then 1 else NULL end)) + Count((case when P.Tarde = 'P' then 1 else NULL end)) AS 'Presenca', "; 
        $strSQL .= "Count((case when P.Manha = 'F' then 1 else NULL end)) + Count((case when P.Tarde = 'F' then 1 else NULL end)) AS 'Falta', "; 
        $strSQL .= "((Count((case when P.Manha = 'P' then 1 else NULL end)) + Count((case when P.Tarde = 'P' then 1 else NULL end)))*100) / ((DateDiff(C.DtFimCurso, C.DtIniCurso)+1)*2) AS '%', ";
        $strSQL .= "If(C.DtAprovacao = '0000-00-00', 'Reprovado', 'Aprovado') AS 'Situacao' ";
        $strSQL .= "FROM cadastro C  ";
        $strSQL .= "LEFT JOIN presenca P ON C.CPF = P.CPF AND C.Curso = P.Curso AND C.NIP = P.NIP AND C.Turma = P.Turma ";
        $strSQL .= "WHERE C.Curso = '$cCurso' ";
        $strSQL .= "AND C.Turma = '$cTurma' ";

        if($pNome != "")
            $strSQL .= "AND Nome LIKE '%$pNome%'";
        if($pCpf != "")
            $strSQL .= "AND CPF = '$pCpf'";
        
        $strSQL .= "GROUP BY C.Nome, C.EMail ";
        $strSQL .= "ORDER BY C.Nome";
        
        $Retorno = $ClsBD->ExecutarQry($strSQL, "S");
        return $Retorno;
    }    
}
?>